package DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import Model.Subscription;


public class DBSubscription implements IFDBSubscription{
	private static Connection con;
	Statement stmt;
	public DBSubscription() 
	{
		con = DBCon.getInstance().getDBCon();
	}
	/* (non-Javadoc)
	 * @see DB.IFBDSubscription#createSubscription(int, java.lang.String, int, int)
	 */
	@Override
	public void createSubscription(String type,int price, int discount)
	{
		try{
			int mID=GetMax.getMaxId("Select max(mID) from Subscription")+1;//Gets the maximum mID from the DB and adds+1 for the next ID
			String query ="INSERT INTO Subscription(mID, type, price, discount)  VALUES('"+mID+"','"+type+"','"+price+"','"+discount+"')";
			 Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
			}
	}
	/* (non-Javadoc)
	 * @see DB.IFBDSubscription#deleteSubscription(int)
	 */
	@Override
	public void deleteSubscription(int mID)
	{	//mID=1; 
		try{
			String query="DELETE FROM Subscription WHERE mID = '"+mID+"'";
			Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
		}
		catch(Exception e)
		{e.printStackTrace();
		}
	}
	/* (non-Javadoc)
	 * @see DB.IFBDSubscription#updateSubscription(Model.Subscription)
	 */
	@Override
	public void updateSubscription(Subscription g, int choice)
	{ Subscription suObj = g;
	  boolean exec =false;
          	String query="";
          	switch(choice){
          	case 0: //Update First Name
          		query="UPDATE Subscription SET "+
          			 	  "type ='"+ suObj.getType()+"'"+
          			 	  " WHERE mID = '"+ suObj.getmID() + "'";
          			 	 exec =true;
          			 	 break;
          	case 1: //Update First Name
          		query="UPDATE Subscription SET "+
          			 	  "price ='"+ suObj.getPrice()+"'"+
          			 	  " WHERE mID = '"+ suObj.getmID() + "'";
          			 	 exec =true;
          			 	 break;
          	case 2://Update Last Name
          		query="UPDATE Subscription SET "+
          				"discount ='"+ suObj.getDiscount()+"'"+
          			 	 " WHERE mID = '"+ suObj.getmID() + "'";
          			 	 exec =true;
          			 	 break;
         	case 4://Update All
        		query="UPDATE Subscription SET "+
        			 	  "type ='"+ suObj.getType()+"', "+
        			 	  "price ='"+ suObj.getPrice() + "'" +
        			 	 "discount ='"+ suObj.getDiscount() + "'" +
        			          " WHERE mID = '"+ suObj.getmID() + "'";
        		exec=true;
        		break;}
        	
        	
        	
        	while(exec)
        	{ exec =false;
                      try{
                    	  Statement stmt = con.createStatement();
                    	  stmt.setQueryTimeout(5);
                    	  stmt.executeUpdate(query);
                    	  stmt.close();
                    	  
                      }
                      catch(Exception e)
                      {e.printStackTrace();}
        	}
	}
	
	private String buildQuery()
	{
	    String query="SELECT mID, type, price, discount   FROM Subscription";
		/*
		if (wClause.length()>0)
			query=query+" WHERE ";
			*/
		return query;
	}
	/* (non-Javadoc)
	 * @see DB.IFBDSubscription#miscWhere()
	 */
	@Override
	public ArrayList<Subscription> miscWhere()
	{
            ResultSet results;
	    ArrayList<Subscription> list = new ArrayList<Subscription>();	
		
	    String query =  buildQuery();
  
            try{ // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 Subscription mObj = new Subscription();
		         mObj = buildSubscription(results);	
                 list.add(mObj);	
		}//end while
                 stmt.close();     
                 
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	

	@Override
	public Subscription findSubscription(int mID)
	{
		ResultSet results;
		Subscription mObj = new Subscription();
                
	        String query =  buildQuery() + " WHERE mID ="+"'"+mID+"'";
                System.out.println(query);
		try{ // 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            mObj = buildSubscription(results);
                            
                            stmt.close();
                            
                            
			}
                        else{ //no Subscription was found
                            mObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return mObj;
	}
	private Subscription buildSubscription(ResultSet results)
    {   Subscription suObj = new Subscription();
        try{ // 
              suObj.setmID(results.getInt("mID"));
              suObj.setType(results.getString("type"));
              suObj.setPrice(results.getInt("price"));
              suObj.setDiscount(results.getInt("discount"));
            
              
        }
       catch(Exception e)
       {	e.printStackTrace();
           System.out.println("error in building the Subscription object");
       }
       return suObj;
}
	
}
